package edu.bu.bga.db;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;
import java.util.StringTokenizer;

import android.content.ContentValues;
import android.content.Context;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import edu.bu.bga.rule.Rule;
import edu.bu.bga.rule.SimpleTextRule;

public class DatabaseHelper extends SQLiteOpenHelper {

	private static final String dbName = "bdaDB";
	private static final int currentVersion = 1;

	private static final String t_Rules_table = "t_Rules";
	public static final String t_Rules_Text = "t_Rules_Text";
	private static final String t_Rules_ID = "_id";
	private static final String t_Users_table = "t_Users";
	private static final String t_Users_Text = "t_Users_Text";
	private static final String t_Users_ID = "t_Users_ID";
	private static final String t_UserRuleHist_table = "t_UserRuleHist";
	private static final String t_UserRuleHist_UserID = "t_UserRuleHist_UserID";
	private static final String t_UserRuleHist_RuleID = "t_UserRuleHist_RuleID";
	private static final String t_UserRuleHist_DTStamp = "t_UserRuleHist_DTStamp";
	private static final String tg_UserRuleHist_delete = "tg_UserRuleHist_delete";
	private static final String adminUserID = "1";

	// asset file name
	private static final String fileName = "Rules_Data.txt";

	private Resources resources;

	public DatabaseHelper(Context context) {
		super(context, dbName, null, currentVersion);
		resources = context.getResources();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		createTables(db);

		// insert the initial rules
		insertInitialRules(db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		dropTables(db);
		onCreate(db);
	}

	public Rule getRule() {

		Cursor cursor = getRuleCursor();

		int rulesIndex = cursor.getColumnIndex(t_Rules_Text);

		cursor.moveToFirst();

		String ruleText = cursor.getString(rulesIndex);
		SimpleTextRule rule = new SimpleTextRule(ruleText);
		cursor.close();

		return rule;
	}

	public Cursor getRuleCursor() {

		// get a handle to the db
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor bgaCursor = db
				.rawQuery(
						"SELECT * FROM t_Rules where _id not in "
								+ "(select t_UserRuleHist_RuleID from t_UserRuleHist where"
								+ " t_UserRuleHist_UserID = 1) ORDER BY "
								+ "RANDOM() LIMIT 1;", new String[] {});
		return bgaCursor;
	}

	public List<Rule> getAllRules() {
		List<Rule> rules = new ArrayList<Rule>();

		Cursor cursor = getAllRulesCursor();

		int rulesIndex = cursor.getColumnIndex(t_Rules_Text);

		cursor.moveToFirst();
		while (cursor.isAfterLast() == false) {
			String ruleText = cursor.getString(rulesIndex);
			rules.add(new SimpleTextRule(ruleText));
			cursor.moveToNext();
		}
		cursor.close();

		return rules;
	}

	public Cursor getAllRulesCursor() {
		// get a handle to the db
		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db.rawQuery("SELECT * FROM " + t_Rules_table,
				new String[] {});

		return cursor;
	}

	public void insertRule(String ruleText) {
		// get a handle to the db
		SQLiteDatabase db = this.getWritableDatabase();
		// insert rule
		insertRule(db, ruleText);
	}

	public void deleteRule(String id) {
		// get a handle to the db
		SQLiteDatabase db = this.getWritableDatabase();
		// insert rule
		deleteRule(db, id);
	}

	public void resetTables() {
		// get a handle to the db
		SQLiteDatabase db = this.getWritableDatabase();

		// clear db
		clearTables(db);

		// add initial rules
		insertInitialRules(db);

		// close db
		db.close();
	}

	protected void createTables(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + t_Rules_table + " (" + t_Rules_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + t_Rules_Text
				+ " TEXT);");
		db.execSQL("CREATE TABLE " + t_Users_table + " (" + t_Users_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + t_Users_Text
				+ " TEXT);");
		db.execSQL("CREATE TABLE " + t_UserRuleHist_table + " ("
				+ t_UserRuleHist_UserID + " INTEGER, " + t_UserRuleHist_RuleID
				+ " INTEGER, " + t_UserRuleHist_DTStamp
				+ "DATE DEFAULT (datetime('now','localtime')));");

		// Here we create a trigger that deletes the oldest rule in
		// t_UserRuleHist_table after the trigger decides that this table
		// contains more than half of all available rules in t_Rules_table

		db.execSQL("CREATE TRIGGER " + tg_UserRuleHist_delete
				+ " AFTER INSERT ON " + t_UserRuleHist_table + " BEGIN "
				+ "DELETE FROM " + t_UserRuleHist_table + " WHERE "
				+ t_UserRuleHist_UserID + " = " + adminUserID
				+ " AND CAST((SELECT COUNT(*) FROM " + t_UserRuleHist_table
				+ " WHERE " + t_UserRuleHist_UserID + " = " + adminUserID
				+ " ) as REAL)/CAST((SELECT COUNT(*) FROM " + t_Rules_table
				+ " ) as REAL) > .50 and " + t_UserRuleHist_DTStamp
				+ " = (select min( " + t_UserRuleHist_DTStamp + " ) from "
				+ t_UserRuleHist_table + " where " + t_UserRuleHist_UserID
				+ " = " + adminUserID + "); END;");

	}

	protected void dropTables(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + t_Rules_table + ";");
		db.execSQL("DROP TABLE IF EXISTS " + t_Users_table + ";");
		db.execSQL("DROP TABLE IF EXISTS " + t_UserRuleHist_table + ";");
		db.execSQL("DROP TRIGGER IF EXISTS " + tg_UserRuleHist_delete + ";");
	}

	protected void clearTables(SQLiteDatabase db) {
		db.execSQL("DELETE FROM " + t_Rules_table + ";");
		db.execSQL("DELETE FROM " + t_Users_table + ";");
		db.execSQL("DELETE FROM " + t_UserRuleHist_table + ";");
	}

	protected void deleteRule(SQLiteDatabase db, String id) {
		db.execSQL("DELETE FROM " + t_Rules_table + " WHERE " + t_Rules_ID
				+ " = " + id + "; ");
	}

	protected void deleteUsers(SQLiteDatabase db, String Users_ID_To_Delete) {
		db.execSQL("DELETE FROM " + t_Users_table + " WHERE " + t_Users_ID
				+ " = " + Users_ID_To_Delete + "; ");
	}

	protected void insertRuleHist(SQLiteDatabase db,
			String t_UserRuleHist_UserID, String t_UserRuleHist_Text) {
		db.execSQL("INSERT INTO " + db + "( " + t_UserRuleHist_UserID + ", "
				+ t_UserRuleHist_RuleID + ") VALUES ( " + 1 + ", "
				+ t_UserRuleHist_RuleID + ")");

	}

	protected long insertRule(SQLiteDatabase db, String text) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(t_Rules_Text, text);
		return db.insert(t_Rules_table, null, initialValues);
	}

	protected void insertInitialRules(SQLiteDatabase db) {

		BufferedReader br = null;

		try {
			// get the file as a stream
			br = new BufferedReader(new InputStreamReader(resources.getAssets()
					.open(fileName)));

			String str;
			while ((str = br.readLine()) != null) {
				StringTokenizer st = new StringTokenizer(str, ",");

				// get index
				String index = st.nextToken();

				// get text
				String text = st.nextToken();

				String ruleText = "Player with the " + text + " goes first";

				// insert rule
				insertRule(db, ruleText);
			}

		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (br != null) {
				try {
					br.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
}
